<?php
/**
 * @Author:         zk
 * @DateTime:       18-9-6 下午2:19
 * @Description:    获取门店库存预警分析表统计数据
 */
//header("Content-type: text/html; charset=utf-8");
$api_service_path = __DIR__ . "/../../../api/";
ob_start();
require_once($api_service_path . "public/index.php");
ob_end_clean();
set_time_limit(0);
//DB::connection()->disableQueryLog();      //关闭日志

$now_date = date('Y-m-d', time());
$first = 1;     // 1 表示每周星期一为开始日期、0 表示每周日为开始日期
$this_week_date = date('w', time());    //获取当前周的第几天 周日是 0 周一到周六是 1 - 6
$this_week_start_str = $now_date.' - '.($this_week_date ? ($this_week_date - $first) : 6).' days';   //获取本周开始日期，如果$this_week_date是0，则表示周日，减去 6 天
$this_week_start = date('Y-m-d', strtotime($this_week_start_str));
$last_week_start = date('Y-m-d', strtotime("$this_week_start - 7 days"));   //上周开始时间  上周一
$last_week_end = date('Y-m-d', strtotime("$this_week_start -1 days"));      //上周结束时间  上周日

$shops_info = MaintenanceShops::getAllShopInfo();   //获取所有有效的门店信息
if(!empty($shops_info)){
    foreach ($shops_info as $k => $v) {
        $shop_id = $v['id'];
        $shop_name = $v['name'];
        $res = warehouseWarnAnalyze($shop_id, $last_week_start, $last_week_end);
        if($res['res']){
            Log::info('门店ID:'.$shop_id.', 门店名称: '.$shop_name.' msg: '.$res['msg'].'');
            echo '门店ID:'.$shop_id.', 门店名称: '.$shop_name.' msg: '.$res['msg']." \n";
        } else {
            Log::error('门店ID:'.$shop_id.', 门店名称: '.$shop_name.' msg: '.$res['msg'].' error');
            echo '门店ID:'.$shop_id.', 门店名称: '.$shop_name.' msg: '.$res['msg']." error \n";
        }
    }
} else {
    Log::error('获取所有有效的门店信息 error');
    echo "获取所有有效的门店信息 error \n";
}
        
/**
 * @Author:          zk
 * @DateTime:        18-9-6 下午2:21
 * @Description:     门店库存预警分析统计
 * @param int $shop_id          门店ID
 * @param string $start_date    统计分析开始时间
 * @param string $end_date      统计分析结束时间
 * @return array res:TRUE/FALSE; msg:结果说明;
 */
function warehouseWarnAnalyze($shop_id, $start_date, $end_date) {
    $result = [
        'res' => TRUE, 'msg' => 'ok',
    ];
    $now_time = date('Y-m-d H:i:s', time());
    
    //查询出符合条件的耗材商品
    //只显示门店系统库存警报模块内设置库存阈值的商品，且在库存警报页面中监控状态为“开启”，并且商品库存超过设置的库存阈值的商品

    //step 1 查找库存上下限 start
    $select_warn = [
        'maintenance_warehouse_warn.item_id', 'maintenance_warehouse_warn.item_type', 
        'maintenance_warehouse_warn.max_number', 'maintenance_warehouse_warn.mim_number AS min_number',
    ];
    $warn_item = MaintenanceWarehouseWarn::join('supply_item', 'maintenance_warehouse_warn.item_id', '=', 'supply_item.id')
            ->where('maintenance_warehouse_warn.shop_id', $shop_id)
            ->where('maintenance_warehouse_warn.status', 1)
            ->where('supply_item.status', 1)
            ->select($select_warn)
            ->get()
            ->toArray();
    if(empty($warn_item)){
        $result = [
            'res' => FALSE, 
            'msg' => '门店库存预警查询为空',
        ];
        return $result;
    }
    $warn_item_config = [];   //门店预警配置的耗材ID 数组
    $warn_item_data = [];   //拼装后的门店耗材的上下限参考值数组
    foreach ($warn_item as $v) {
        $key = $v['item_type'].'-'.$v['item_id'];
        $warn_item_data[$key] = $v;
        $warn_item_config[] = $v['item_id'];
    }
    //step 1 查找库存上下限 end

    //step 2 查询门店的上周各种耗材库存、库存成本 start
    $select_storage_item = 'item_id, item_type, sum(current_number) AS number, sum(current_number*tax_purchase_price) AS amount';
    $storage_item = MaintenanceStorageItem::where('shop_id', $shop_id)
            ->where('status', 1)
            ->where('type', 1)
            ->whereIn('item_id', $warn_item_config)
            ->selectRaw($select_storage_item)
            ->groupBy('item_type')
            ->groupBy('item_id')
            ->get()
            ->toArray();
    if(empty($storage_item)){
        $result = [
            'res' => FALSE, 
            'msg' => '门店入库耗材记录查询为空',
        ];
        return $result;
    }      
    $storage_item_data = [];    //拼装后的门店耗材的库存、库存成本数组
    foreach ($storage_item as $v) {
        $key = $v['item_type'].'-'.$v['item_id'];
        $storage_item_data[$key] = $v;

        $max_number = isset($warn_item_data[$key]['max_number']) ? $warn_item_data[$key]['max_number'] : 0;
        $min_number = isset($warn_item_data[$key]['min_number']) ? $warn_item_data[$key]['min_number'] : 0;
        //去除在正常范围值内的耗材ID
        if(($v['number'] >= $min_number) && ($v['number'] <= $max_number)){
            $temp_key = array_search($v['item_id'], $warn_item_config);
            unset($warn_item_config[$temp_key]);
        }
    }
    //step 2 查询门店的上周各种耗材库存、库存成本 end

    if(empty($warn_item_config)){
        $result['msg'] = '需要库存预警的耗材的库存都在正常范围值以内';
        return $result;
    }

    //构造货位数组  start
    $select_location = [
        'item_id', 'item_type', 'location',
    ];
    $location = SupplyLocation::where('shop_id', $shop_id)
            ->whereIn('item_id', $warn_item_config)
            ->select($select_location)
            ->get()
            ->toArray();
    if(empty($location)){
        $result = [
            'res' => FALSE, 
            'msg' => '门店耗材货位查询为空',
        ];
        return $result;
    }
    $location_data = [];    //拼装后的门店耗材的货位数组
    foreach ($location as $v) {
        $key = $v['item_type'].'-'.$v['item_id'];
        $location_data[$key] = $v;
    }
    //构造货位数组  end

    //开启事务
    DB::beginTransaction();
    try{
        //step 3 添加库存预警消息 到站内信 start

        //站内信添加 start
        $notice_name = $start_date.'-'.$end_date.'库存预警报告';        //标题
        $notice_id = ScNotice::insertGetId([
            'activity_base_id' => 0, 
            'name' => $notice_name, 
            'status' => 1, 
            'desc' => $notice_name, 
            'notice_type' => 5, 
            'created_type' => 4, 
            'created_by' => 0, 
            'created_at' => $now_time,
        ]);
        if(!$notice_id){
            DB::rollBack();
            $result = [
                'res' => FALSE, 
                'msg' => '门店库存预警站内信添加失败',
            ];
            return $result;
        }
        //站内信添加 end

        //站内信关联门店添加 start
        $notice_shop = new ScNoticeShop();
        $notice_shop->notice_id = $notice_id;
        $notice_shop->activity_base_id = 0;
        $notice_shop->shop_id = $shop_id;
        $notice_shop->created_by = 0;
        $notice_shop->status = 1;
        $notice_shop->created_at = $now_time;
        $notice_shop_res = $notice_shop->save();
        if(!$notice_shop_res){
            DB::rollBack();
            $result = [
                'res' => FALSE, 
                'msg' => '站内信关联门店添加失败',
            ];
            return $result;
        }
        //站内信关联门店添加 end

        //step 3 添加库存预警消息 到站内信 end

        //step 4 添加库存预警具体耗材信息到 门店库存预警分析表 end
        //查询出相应的耗材信息
        $select_item_arr = [
            'supply_item.id', 'supply_item.code', 'supply_item.old_code', 'supply_item.supply_classification_id', 
            'supply_classification.name AS supply_classification_name', 'supply_item.brand_id', 'supply_brand.brand_name', 
            'supply_item.series_id', 'supply_series.name AS series_name', 'supply_item.sale_unit_id', 
            'sale_unit.name AS sale_unit_name', 'supply_item.model', 'supply_item.volume_size', 'supply_item.status', 
            'supply_item.car_model_comments', 'supply_item.name', 'supply_item.type', 
            'lev2.id AS level_2_id', 'lev2.name AS level_2_name', 'lev1.id AS level_1_id', 'lev1.name AS level_1_name', 
        ];
        $item_info = SupplyItem::leftJoin('anhui_data.supply_series', 'anhui_data.supply_series.id', '=', 'supply_item.series_id')
                ->leftJoin('anhui_data.supply_brand', 'anhui_data.supply_brand.id', '=', 'supply_item.brand_id')
                ->leftJoin('anhui_data.supply_unit AS sale_unit', 'sale_unit.id', '=', 'supply_item.sale_unit_id')
                ->leftJoin('supply_classification', 'supply_classification.id', '=', 'supply_item.supply_classification_id')
                ->leftJoin('supply_classification AS lev2', 'supply_classification.parent_id', '=', 'lev2.id')
                ->leftJoin('supply_classification AS lev1', 'lev2.parent_id', '=', 'lev1.id')
                ->select($select_item_arr)
                ->whereIn('supply_item.id', $warn_item_config)
                ->get()
                ->toArray();
        if(empty($item_info)){
            DB::rollBack();
            $result = [
                'res' => FALSE, 
                'msg' => '耗材详情查询为空',
            ];
            return $result;
        }

        $insert_warehouse_warn_analyze_data = [];   //门店库存预警分析表待插入数据
        foreach ($item_info as $v) {
            $key = $v['type'].'-'.$v['id'];   //拼装统一的key

            $insert_warehouse_warn_analyze_data[] = [
                'shop_id' => $shop_id, 
                'item_id' => $v['id'], 
                'item_name' => $v['name'], 
                'supply_classification_id' => $v['supply_classification_id'], 
                'supply_classification_name' => $v['supply_classification_name'], 
                'level_1_id' => $v['level_1_id'], 
                'level_1_name' => $v['level_1_name'], 
                'level_2_id' => $v['level_2_id'], 
                'level_2_name' => $v['level_2_name'], 
                'level_3_id' => $v['supply_classification_id'], 
                'level_3_name' => $v['supply_classification_name'], 
                'item_code' => $v['code'], 
                'brand_id' => $v['brand_id'], 
                'brand_name' => $v['brand_name'], 
                'series_id' => $v['series_id'], 
                'series_name' => $v['series_name'], 
                'model' => $v['model'], 
                'sale_unit_id' => $v['sale_unit_id'],
                'sale_unit_name' => $v['sale_unit_name'], 
                'car_model_comments' => $v['car_model_comments'], 
                'location' => isset($location_data[$key]['location']) ? $location_data[$key]['location'] : '', 
                'storage_number' => isset($storage_item_data[$key]['number']) ? $storage_item_data[$key]['number'] : 0, 
                'storage_amount' => isset($storage_item_data[$key]['amount']) ? $storage_item_data[$key]['amount'] : 0, 
                'max_number' => isset($warn_item_data[$key]['max_number']) ? $warn_item_data[$key]['max_number'] : 0, 
                'min_number' => isset($warn_item_data[$key]['min_number']) ? $warn_item_data[$key]['min_number'] : 0, 
                'notice_id' => $notice_id, 
                'start_date' => $start_date, 
                'end_date' => $end_date, 
                'created_at' => $now_time, 
                'created_by' => 0,  //系统
            ];
        }
        //maintenance_warehouse_warn_analyze
        $res_warehouse_warn_analyze = MaintenanceWarehouseWarnAnalyze::insert($insert_warehouse_warn_analyze_data);
        if(!$res_warehouse_warn_analyze){
            DB::rollBack();
            $result = [
                'res' => FALSE, 
                'msg' => '门店库存预警分析耗材数据添加失败',
            ];
            return $result;
        }
        //step 4 添加库存预警具体耗材信息到 门店库存预警分析表 end

        DB::commit();
    } catch (\Exception $e){
        DB::rollBack();
        Log::error('error', ['system' => 'scripts', 'event' => 'get_warehouse_warn_analyze', 'exception' => $e]);
        $result = [
            'res' => FALSE, 
            'msg' => '门店库存预警数据生成失败',
        ];
    }

    return $result;
}



